package com.open.database;

import com.open.model.ColumnData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;


public class MysqlSelectProperty{

    /**
     * 根据数据库名查询数据库所有表名称
     * @param databaseName
     * @return List<String>
     * @throws SQLException
     */
    public List<String> queryTables(String databaseName) throws SQLException {
        List<String> list = new ArrayList<String>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBConnection.getConnection();
            ps = con.prepareStatement(DBTypeToBeanMapping.SQLTABLES);
            ps.setString(1, databaseName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                list.add(tableName);
            }
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
        return list;
    }

    /**
     * 查询数据库表所有的列
     * @param databaseName tableName
     * @return List<ColumnData>
     * @throws SQLException
     */
    public List<ColumnData> queryColumnDatas(String databaseName, String tableName) throws SQLException {
        List<ColumnData> columnList = new ArrayList<ColumnData>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBConnection.getConnection();
            ps = con.prepareStatement(DBTypeToBeanMapping.SQLCOLUMNS);
            ps.setString(1, databaseName);
            ps.setString(2, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String name = rs.getString(1);
                String type = rs.getString(2);
                String comment = rs.getString(3);
                Boolean required = Objects.equals(rs.getString(4), "NO");// required
                String jdbcType = DBTypeToBeanMapping.convertType(type);
                String propertyName = DBTypeToBeanMapping.columnProperty(name);
                ColumnData cd = new ColumnData();
                cd.setColumnName(name);
                cd.setDataType(type);
                cd.setJdbcType(jdbcType);
                cd.setColumnComment(comment);
                cd.setPropertyName(propertyName);
                cd.setRequired(required);
                columnList.add(cd);
            }
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
        return columnList;
    }

    /**
     * 查询当前表的主键
     * @param tableName databaseName
     * @return Map<String,ColumnData>
     * @throws SQLException
     */
    public Map<String, ColumnData> queryTablePeKey(String tableName, String databaseName) throws SQLException {
        Map<String, ColumnData> priyKey = new HashMap<String, ColumnData>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBConnection.getConnection();
            ps = con.prepareStatement(DBTypeToBeanMapping.SQL_PRIMARY_KEY);
            ps.setString(1, tableName);
            ps.setString(2, databaseName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String name = rs.getString(1);
                String type = rs.getString(2);
                String comment = rs.getString(3) + "";
                String auto = rs.getString(4) + "";
                String jdbcType = DBTypeToBeanMapping.convertType(type);
                ColumnData cd = new ColumnData();
                cd.setColumnName(name);
                cd.setDataType(type);
                cd.setJdbcType(jdbcType);
                cd.setColumnComment(comment.replaceAll("\n", ""));
                if ("auto_increment".equalsIgnoreCase(auto)) {
                    cd.setAuto(true);
                }
                String propertyName = DBTypeToBeanMapping.columnProperty(name);
                cd.setPropertyName(propertyName);
                String maxChar = propertyName.substring(0, 1).toUpperCase();
                String method = "get" + maxChar + propertyName.substring(1, propertyName.length());
                cd.setPkget(method);
                priyKey.put(name, cd);
            }
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
        return priyKey;
    }

}
